package biz.bbtec.sbm.store.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import biz.bbtec.sbm.db.dbConnection;
import biz.bbtec.sbm.store.bean.AddressBean;
import biz.bbtec.sbm.store.bean.CheckBean;
import biz.bbtec.sbm.store.bean.ItemBean;
import biz.bbtec.sbm.store.bean.StoreBean;
import biz.bbtec.sbm.user.bean.UserBean;

public class CheckService {

	dbConnection db = new dbConnection();

	public List showallcheck(UserBean user, int type) {
		List<CheckBean> list = new ArrayList<CheckBean>();

		String sql1 = null;
		if ((type == 0) || (type == 2)) {
			sql1 = "select checkin.*,"
					+ "item.name as itemname,address.name as addname,user.name as username,item.unit as itemunit "
					+ "from checkin,user,address,item where checkin.userid = user.userid "
					+ "and checkin.itemid = item.itemid and checkin.adsid=address.adsid "
					+ "and checkin.orgid = ? order by date desc,chinid desc";
		} else {
			sql1 = "select checkout.*,"
					+ "item.name as itemname,address.name as addname,user.name as username,item.unit as itemunit "
					+ "from checkout,user,address,item where checkout.userid = user.userid "
					+ "and checkout.itemid = item.itemid and checkout.adsid=address.adsid "
					+ "and checkout.orgid = ? order by date desc,choutid desc";
		}

		Connection conn = db.getConnection();
		PreparedStatement past = null;
		ResultSet rs = null;

		try {
			past = conn.prepareStatement(sql1);
			past.setInt(1, user.getOrgid());
			rs = past.executeQuery();
			while (rs.next()) {
				CheckBean check = new CheckBean();
				check.setAdsid(rs.getInt("adsid"));
				if ((type == 0) || (type == 2)) {
					check.setChinid(rs.getInt("chinid"));
				} else {
					check.setChoutid(rs.getInt("choutid"));
				}
				check.setItemid(rs.getInt("itemid"));
				check.setUnitcost(Double.parseDouble(rs.getString("unitcost")));
				check.setQty(rs.getDouble("qty"));
				check.setPrice(check.getQty() * check.getUnitcost());
				check.setAddname(rs.getString("addname"));
				check.setItemname(rs.getString("itemname"));
				check.setUsername(rs.getString("username"));
				check.setOrgid(rs.getInt("orgid"));
				check.setDate(rs.getString("date"));
				check.setUnit(rs.getString("itemunit"));
				list.add(check);
			}
			rs.close();
			past.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return list;
	}

	public boolean checkin(CheckBean check, UserBean user) {
		boolean flag = false;

		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		Date date = new Date();
		String thisDay = sdf.format(date);

		String sql1 = "insert into checkin(itemid,adsid,qty,userid,unitcost,orgid,date) values("
				+ "'"
				+ check.getItemid()
				+ "'"
				+ ","
				+ "'"
				+ check.getAdsid()
				+ "'"
				+ ","
				+ "'"
				+ check.getQty()
				+ "'"
				+ ","
				+ "'"
				+ user.getUserid()
				+ "'"
				+ ","
				+ "'"
				+ check.getUnitcost()
				+ "'"
				+ ","
				+ "'"
				+ user.getOrgid()
				+ "'"
				+ ","
				+ "'"
				+ thisDay
				+ "'" + ")";
		String sql2 = "insert into store(itemid,adsid,qty,unitcost) values("
				+ "'" + check.getItemid() + "'" + "," + "'" + check.getAdsid()
				+ "'" + "," + "'" + check.getQty() + "'" + "," + "'"
				+ check.getUnitcost() + "'" + ")";

		Connection conn = db.getConnection();
		PreparedStatement past = null;
		ResultSet rs = null;
		try {
			past = conn.prepareStatement(sql1, Statement.RETURN_GENERATED_KEYS);
			past.executeUpdate(sql1);
			rs = past.getGeneratedKeys();
			int checkid = 0;
			if (rs.next()) {
				checkid = rs.getInt(1);
				rs.close();
			}
			if (check.getAccount() != null) {
				Double total = check.getQty() * check.getUnitcost()
						+ check.getFare();
				String sql3 = "insert into budget(chinid,account,fare,total) values("
						+ "'"
						+ checkid
						+ "'"
						+ ","
						+ "'"
						+ check.getAccount()
						+ "'"
						+ ","
						+ "'"
						+ check.getFare()
						+ "'"
						+ ","
						+ "'"
						+ total + "'" + ")";
				PreparedStatement past2 = null;
				past2 = conn.prepareStatement(sql3);
				past2.executeUpdate();
				past2.close();
			}
			past.close();
			past = conn.prepareStatement(sql2);
			past.executeUpdate(sql2);
			past.close();

			flag = true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return flag;
	}

	public boolean checkout(CheckBean check, UserBean user) {
		boolean flag = false;

		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		Date date = new Date();
		String thisDay = sdf.format(date);

		double sum = 0.0;
		List<StoreBean> list = new ArrayList<StoreBean>();

		String sql1 = "select qty,itemid,adsid,storeid from store where itemid = ? and adsid = ? order by qty asc,unitcost asc,storeid asc";

		String sql2 = "insert into checkout(itemid,adsid,qty,userid,unitcost,orgid,date) values("
				+ "'"
				+ check.getItemid()
				+ "'"
				+ ","
				+ "'"
				+ check.getAdsid()
				+ "'"
				+ ","
				+ "'"
				+ check.getQty()
				+ "'"
				+ ","
				+ "'"
				+ user.getUserid()
				+ "'"
				+ ","
				+ "'"
				+ check.getUnitcost()
				+ "'"
				+ ","
				+ "'"
				+ user.getOrgid()
				+ "'"
				+ ","
				+ "'"
				+ thisDay
				+ "'" + ")";

		Connection conn = db.getConnection();
		PreparedStatement past = null;
		ResultSet rs = null;
		try {
			past = conn.prepareStatement(sql1);
			past.setInt(1, check.getItemid());
			past.setInt(2, check.getAdsid());
			rs = past.executeQuery();
			while (rs.next()) {
				StoreBean store = new StoreBean();
				store.setStoreid(rs.getInt("storeid"));
				store.setQty(Double.parseDouble(rs.getString(1)));
				store.setItemid(rs.getInt(2));
				store.setAdsid(rs.getInt(3));
				list.add(store);
			}
			rs.close();
			past.close();

			for (int i = 0; i < list.size(); i++) {
				sum = sum + list.get(i).getQty();
			}

			if (((int) (sum + 0.5)) < ((int) (check.getQty() + 0.5))) {
				return false;
			} else {
				sum = check.getQty();
				for (int i = 0; i < list.size(); i++) {
					double qty = list.get(i).getQty();
					if ((int) (qty + 0.5) <= ((int) (sum + 0.5))) {
						sum = sum - qty;
						String sql4 = "delete from store where itemid=? and adsid=? and storeid=?";
						past = conn.prepareStatement(sql4);
						past.setInt(1, list.get(i).getItemid());
						past.setInt(2, list.get(i).getAdsid());
						past.setInt(3, list.get(i).getStoreid());
						past.executeUpdate();
						past.close();
					} else {
						String sql4 = "update store set qty=? where itemid=? and adsid=? and storeid=?";
						past = conn.prepareStatement(sql4);
						past.setDouble(1, qty - sum);
						past.setInt(2, list.get(i).getItemid());
						past.setInt(3, list.get(i).getAdsid());
						past.setInt(4, list.get(i).getStoreid());
						past.executeUpdate();
						past.close();
						break;
					}
				}
			}

			past = conn.prepareStatement(sql2, Statement.RETURN_GENERATED_KEYS);
			past.executeUpdate(sql2);
			ResultSet rs2 = past.getGeneratedKeys();
			int checkid = 0;
			if(rs2.next()){
				checkid = rs2.getInt(1);
				rs2.close();
			}
			past.close();
			
			double total = check.getQty()*check.getUnitcost()+check.getFare();
			String sql6 =  "insert into budget(chinid,account,fare,total) values("
					+ "'"
					+ checkid
					+ "'"
					+ ","
					+ "'"
					+ check.getAccount()
					+ "'"
					+ ","
					+ "'"
					+ check.getFare()
					+ "'"
					+ ","
					+ "'"
					+ total + "'" + ")";
			
			flag = true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return flag;
	}

	public List showalliteminfo(UserBean user) {
		List<ItemBean> list = new ArrayList<ItemBean>();

		String sql1 = "select name,itemid from item where orgid=?";
		Connection conn = db.getConnection();
		PreparedStatement past = null;
		ResultSet rs = null;

		try {
			past = conn.prepareStatement(sql1);
			past.setInt(1, user.getOrgid());
			rs = past.executeQuery();
			while (rs.next()) {
				ItemBean item = new ItemBean();
				item.setItemid(rs.getInt("itemid"));
				item.setName(rs.getString("name"));
				list.add(item);
			}
			rs.close();
			past.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return list;
	}

	public List showalladdinfo(UserBean user) {
		List<AddressBean> list = new ArrayList<AddressBean>();

		String sql1 = "select name,adsid from address where orgid=?";

		Connection conn = db.getConnection();
		PreparedStatement past = null;
		ResultSet rs = null;

		try {
			past = conn.prepareStatement(sql1);
			past.setInt(1, user.getOrgid());
			rs = past.executeQuery();
			while (rs.next()) {
				AddressBean add = new AddressBean();
				add.setAdsid(rs.getInt("adsid"));
				add.setName(rs.getString("name"));
				list.add(add);
			}
			rs.close();
			past.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return list;
	}
}
